package edu.jptest.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.jptest.bean.Favorite;
import edu.jptest.util.GetDataBaseConnection;

public class FavoriteDAO {
	// 插入收藏夹
	public boolean insertFavorite(Favorite favorate) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "insert into favorites(user_id,question_id,collect_time,comment,label_id) values(?,?,?,?,?,?,?)";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, favorate.getUserId());
			pstm.setInt(2, favorate.getQuestionId());
			pstm.setString(4, favorate.getComment());
			pstm.setInt(5, favorate.getLableId());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 删除收藏夹
	public boolean deleteFavorite(int favorite_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "delete from favorites where favorites_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, favorite_id);
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 更该收藏夹
	public boolean updateFavorite(Favorite favorate) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		PreparedStatement pstm = null;
		try {
			String sql = "update favorites set user_id=?,question_id=?,collect_time=?,comment=?,label_id=? where favorites_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, favorate.getUserId());
			pstm.setInt(2, favorate.getQuestionId());
			pstm.setDate(3, favorate.getCollectTime());
			pstm.setString(4, favorate.getComment());
			pstm.setInt(5, favorate.getLableId());
			pstm.setInt(6, favorate.getFavoritesId());
			int result = pstm.executeUpdate();
			if (result == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
		}
		return false;
	}

	// 通过收藏夹标签，查询该标签下所有收藏id
	public List<Integer> getFavoriteIdByFavoriteLabel(int label_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> favoritesId = new ArrayList<Integer>();
		try {
			String sql = "select favorites_id from favorites where label_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, label_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				favoritesId.add(resultSet.getInt(1));
			}
			return favoritesId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过id，查询收藏夹对象
	public Favorite getFavoriteByFavoriteId(int favorite_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		Favorite favorite = null;
		PreparedStatement pstm = null;
		try {
			String sql = "select * from favorites where favorites_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, favorite_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				favorite.setFavoritesId(resultSet.getInt(1));
				favorite.setUserId(resultSet.getInt(2));
				favorite.setQuestionId(resultSet.getInt(3));
				favorite.setCollectTime(resultSet.getDate(4));
				favorite.setComment(resultSet.getString(5));
				favorite.setLableId(resultSet.getInt(6));
				return favorite;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 通过用户Id，获得收藏id
	public List<Integer> getFavoriteIdByUserId(int user_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> favoritesId = new ArrayList<Integer>();
		try {
			String sql = "select favorites_id from favorites where user_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, user_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				favoritesId.add(resultSet.getInt(1));
			}
			return favoritesId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 获得某时间段的收藏id
	public List<Integer> getFavoriteIdByTime(java.sql.Date startDate,
			java.sql.Date endDate) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> favoritesId = new ArrayList<Integer>();
		try {
			String sql = "select favorites_id from favorites where collect_time between ? and ?";// ////
			pstm = connection.prepareStatement(sql);
			pstm.setDate(1, startDate);
			pstm.setDate(2, endDate);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				favoritesId.add(resultSet.getInt(1));
			}
			return favoritesId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 根据用户Id，获得某用户的所有收藏标签id
	public List<Integer> getLabelsIdByUserId(int user_id) {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> favoritesId = new ArrayList<Integer>();
		try {
			String sql = "select label_id from favorites where user_id=?";
			pstm = connection.prepareStatement(sql);
			pstm.setInt(1, user_id);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				favoritesId.add(resultSet.getInt(1));
			}
			return favoritesId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}

	// 获得所有收藏夹id
	public List<Integer> getAllFavoritesId() {
		GetDataBaseConnection getDataBaseConnection = new GetDataBaseConnection();
		Connection connection = getDataBaseConnection.getConnection();
		ResultSet resultSet = null;
		PreparedStatement pstm = null;
		List<Integer> favoritesId = new ArrayList<Integer>();
		try {
			String sql = "select favorites_id from favorites";
			pstm = connection.prepareStatement(sql);
			resultSet = pstm.executeQuery();
			while (resultSet.next()) {
				favoritesId.add(resultSet.getInt(1));
			}
			return favoritesId;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					;
				}
			connection = null;
			if (pstm != null) {
				try {
					pstm.close();
				} catch (SQLException e) {
					;
				}
			}
			pstm = null;
			if (resultSet != null) {
				try {
					resultSet.close();
				} catch (SQLException e) {
					;
				}
			}
			resultSet = null;
		}
		return null;
	}
}
